﻿CREATE PROCEDURE [dbo].[proc_Customer_GetList_MnemonicWorksRepeat] 
	(
		@MnemonicWorks nvarchar(200),
		@CompanyId int,
		@StartIndex int,
		@EndIndex int
	)
AS
Begin
	Declare @cSql nvarchar(3000)
	If @MnemonicWorks<>''
		Set @cSql='(CustomerName Like ''%'+@MnemonicWorks+'%'' Or MnemonicWorks='''+@MnemonicWorks+''')'
	Else
		Set @cSql='MnemonicWorks='''''

	Set @cSql=';WITH list As(Select	ROW_NUMBER() OVER (ORDER BY Id DESC)AS Row,Id,
	CompanyId,CustomerId,ProtectId,Tel,
	IsNull(CustomerName,''...'') As CustomerName,
	IsNull(MnemonicWorks,''...'') As MnemonicWorks,
	IsNull(CreateName,''...'') As CreateUserId,
	IsNull(ProtectName,''...'') As ProtectUserId,
	IsNull(CreateDate,''1900-01-01'') As CreateDate,
	IsNull(ProtectDate,''1900-01-01'') As ProtectDate
	From Customer
	Where CompanyId='+Convert(varchar(10),@CompanyId)+' And Audit=1 And '+@cSql+'
	)

	Select *,(Select Count(0) From List) As RecordCount,
	(Select title From customer_protect Where id=list.ProtectId ) As Protect,
	IsNull((Select Top 1 e.ename From employee e Where e.username=list.CreateUserId),''...'') As CreateName,
	IsNull((Select Top 1 e.ename From employee e Where e.username=list.ProtectUserId),''...'') As ProtectName,
	(Select Count(o.id) From [Order] o Where o.CusId =list.CustomerId) As OrdersCount
	From list
	Where Row Between '+Convert(varchar(10),@StartIndex)+' And '+Convert(varchar(10),@EndIndex)+' Order By Row'

	Exec(@cSql)
End
